<?php

/**
 * @version		$Id: hello.php 15 2009-11-02 18:37:15Z chdemko $
 * @package		Joomla16.Tutorials
 * @subpackage	Components
 * @copyright	Copyright (C) 2005 - 2010 Open Source Matters, Inc. All rights reserved.
 * @author		Christophe Demko
 * @link		http://joomlacode.org/gf/project/helloworld_1_6/
 * @license		License GNU General Public License version 2 or later
 */

// No direct access to this file
defined('_JEXEC') or die('Restricted access');

// import Joomla modelitem library
jimport('joomla.application.component.modelitem');

/**
 * HelloWorld Model
 */
class bgModelbg extends JModel
{
	var $_total = null;
    var $_pagination = null;
    
    public function __construct(){
    	parent::__construct();
 
        $mainframe = JFactory::getApplication();
 
        // Get pagination request variables
        $limit = $mainframe->getUserStateFromRequest('com_frcenter.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
        $limitstart = JRequest::getVar('limitstart', 0, '', 'int');
 
        // In case limit has been changed, adjust it
        $limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);
 
        $this->setState('limit', 5);
        $this->setState('limitstart', $limitstart);
    }
    
    /**
     * get info ocba from id
    **/
    function load($cid){
      
      $qr = "SELECT OCBA_TNAME,OCBA_ADD2,OCBA_REST,OCBA_HEAD,OCBA_PERI,OCBA_TMAIL,OCBA_CMAIL,OCBA_CNAME,OCBA_CODE,OCBA_NAME,OCBA_SHOR,OCBA_KANA,OCBA_IMG,OCBA_POST,OCBA_ADD1,OCBA_TEL,OCBA_FAX,OCBA_LNAME,OCBA_LPOST,OCBA_LADD1,OCBA_LADD2,OCBA_LTEL,OCBA_LFAX,OCBA_CONT,OCBA_ENTR,CLUB_NAME,OCBA_SDATE,OCBA_STIME,OCBA_PR,OCBA_DATE 
                        FROM ocba LEFT JOIN club ON OCBA_CLUB_CODE = CLUB_CODE
                        WHERE OCBA_CODE = '$cid'";
      //Hung: Khong co left join voi club
       /*$qr = "SELECT OCBA_TNAME,OCBA_ADD2,OCBA_HEAD,OCBA_TMAIL,OCBA_CMAIL,OCBA_CNAME,OCBA_CODE,OCBA_NAME,OCBA_SHOR,OCBA_KANA,OCBA_IMG,OCBA_POST,OCBA_ADD1,OCBA_TEL,OCBA_FAX,OCBA_LNAME,OCBA_LPOST,OCBA_LADD1,OCBA_LADD2,OCBA_LTEL,OCBA_LFAX,OCBA_CONT,OCBA_ENTR,CLUB_NAME,OCBA_SDATE,OCBA_STIME,OCBA_PR,OCBA_DATE 
                        FROM ocba 
                        WHERE OCBA_CODE = '$cid'";*/
       
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
       return $result;
    }
    /**
     * get info contact from ocba_code
    **/
    function loadContact($cid){
        $qr = "SELECT OCBA_TNAME,OCBA_TMAIL,OCBA_CMAIL,OCBA_CNAME,OCBA_CODE,OCBA_NAME,OCBA_CNAME,OCBA_CMAIL,OCBA_LADD1,OCBA_LADD2,OCBA_DATE,OCBA_URL,OCBA_MURL,OCBA_HEAD,OCBA_LNAME,OCBA_YNAME,OCBA_YMAIL,OCBA_ENAME,OCBA_EMAIL,OCBA_ONAME,OCBA_OMAIL 
                        FROM ocba
                        WHERE OCBA_CODE = '$cid'";
       
       // load data
       //echo $qr;
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
       return $result;
    }
     /**
     * get info activity from ocba_code
    **/
    function loadActivity($cid){
        $qr = "SELECT OCBA_NAME,ACTT_OCBA_CODE,ACTT_NAME,ACTT_RECR,ACTT_EFFECT,ACTT_CODE,ACTT_FLAG,ACTT_REMARKS,ACTT_PERIOD_START,ACTT_PERIOD_FINISH,ACTT_NUMBER_OF_PARTICIPANTS,ACTT_RECOMMENDED_DEGREE,ACTT_MFAC_CODE_LIST,ACTT_MOBJ_CODE_LIST,ACTT_MCON_CODE_LIST
                    FROM actt inner join ocba ON ocba.OCBA_CODE=actt.ACTT_OCBA_CODE
                    WHERE  ACTT_OCBA_CODE = '$cid'";
       // get total number record
     
       $this->_totalActivity = $this->getTotalActivity($cid);
       
       // order by
       $qr .= " ORDER BY ACTT_CODE DESC"; 
       
       // pagination
       $qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');
       
       // load data
       
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       //load data MFAC
       $qr2 = "SELECT MFAC_CODE,MFAC_NAME FROM mfac";
       $this->_db->setQuery($qr2);
       $list_mfac = $this->_db->loadObjectList();
       //load data MFAC
       $qr3 = "SELECT MOBJ_CODE,MOBJ_NAME FROM mobj";
       $this->_db->setQuery($qr3);
       $list_mobj = $this->_db->loadObjectList();
       //load data MFAC
       $qr4 = "SELECT MCON_CODE,MCON_NAME FROM mcon";
       $this->_db->setQuery($qr4);
       $list_mcon = $this->_db->loadObjectList();
       
       foreach($result as $value){
            //LIST MFAC_NAME
            $type_mfac_name = $value->ACTT_MFAC_CODE_LIST;
            $type_mfac = explode(",",$type_mfac_name);
            $list_mfac_name = '';
            for($i = 0; $i < count($type_mfac); $i++){
                for($j = 0;$j <count($list_mfac); $j++){
                    if($type_mfac[$i] == $list_mfac[$j]->MFAC_CODE){
                        $list_mfac_name .= ",".$list_mfac[$j]->MFAC_NAME;
                    }
                }
            }
            if($list_mfac_name != ''){ $list_mfac_name = substr($list_mfac_name,1); }
            $value->LIST_MFAC = $list_mfac_name;
            //LIST MOBJ
            $type_mobj_name = $value->ACTT_MOBJ_CODE_LIST;
            $type_mobj = explode(",",$type_mobj_name);
            $list_mobj_name = '';
            for($i = 0; $i < count($type_mobj); $i++){
                for($j = 0;$j <count($list_mobj); $j++){
                    if($type_mobj[$i] == $list_mobj[$j]->MOBJ_CODE){
                        $list_mobj_name .= ",".$list_mobj[$j]->MOBJ_NAME;
                    }
                }
            }
            if($list_mobj_name != ''){ $list_mobj_name = substr($list_mobj_name,1); }
            $value->LIST_MOBJ = $list_mobj_name;
            //LIST MCON
            $type_mcon_name = $value->ACTT_MCON_CODE_LIST;
            $type_mcon = explode(",",$type_mcon_name);
            $list_mcon_name = '';
            for($i = 0; $i < count($type_mcon); $i++){
                for($j = 0;$j <count($list_mcon); $j++){
                    if($type_mcon[$i] == $list_mcon[$j]->MCON_CODE){
                        $list_mcon_name .= ",".$list_mcon[$j]->MCON_NAME;
                    }
                }
            }
            if($list_mcon_name != ''){ $list_mcon_name = substr($list_mcon_name,1); }
            $value->LIST_MCON = $list_mcon_name;
                        
       }
       return $result;
    }
    
    /**
     * Get total record in table activity
    **/
    function getTotalActivity($cid){
        // Load the content if it doesn't already exist
        if ($this->_totalActivity === null) {
            
            $query = "SELECT count(DISTINCT ACTT_CODE)
                        FROM actt
                        WHERE  ACTT_OCBA_CODE = '$cid'";
            
            $this->_db->setQuery($query);
            $this->_totalActivity = $this->_db->loadResult();
        }
        
        return $this->_totalActivity;
    } 
    /**
     * Get pagination for club
    **/
    function getPaginationActivity($cid){
        // Load the content if it doesn't already exist
        if (empty($this->_paginationActivity)) {
            jimport('joomla.html.pagination');
            $this->_paginationActivity = new JPagination($this->getTotalActivity($cid), $this->getState('limitstart'), $this->getState('limit') );
        }
        return $this->_paginationActivity;
    }
    
    /**
     * get info OCBA from ocba_code
    **/
    function loadInfoOcba($cid){
        $qr = "SELECT OCBA_CODE,OCBA_NAME,OCBA_KANA,OCBA_SHOR
                        FROM ocba
                        WHERE OCBA_CODE = '$cid'";
       
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
       return $result;
    }
    
    /**
     * get info town from ocba_code
    **/
    function loadTown($cid){
        $qr = "SELECT OCBA_CODE,OCBA_NAME,OCBA_KANA,TOWN_CODE,TOWN_NAME,TOWN_KANA,TOWN_POST,TOWN_ADD1,TOWN_TEL,TOWN_FAX,TOWN_URL,TOWN_NAME,TOWN_ADD2
                        FROM town LEFT JOIN ocba ON OCBA_TOWN_CODE = TOWN_CODE
                        WHERE OCBA_CODE = '$cid'";
       
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
       return $result;
    }

    function loadCenterInTown($town){
       
       $qr = "SELECT OCBA_CODE,OCBA_NAME FROM ocba
                    WHERE OCBA_WEBF=1 AND OCBA_TOWN_CODE = '$town' ";
        $this->_db->setQuery($qr);
        $result = $this->_db->loadObjectList();                
        return $result;
    }
    
    /**
     * get info club from ocba_code
    **/
    function loadClub($cid){
        $qr = "SELECT OCBA_CODE,OCBA_NAME,OCBA_KANA,OCBA_ADD1,OCBA_POST,OCBA_TEL,OCBA_FAX,CLUB_NAME,OCBA_ADD2,OCBA_EFLAG
                        FROM ocba LEFT JOIN club ON OCBA_CLUB_CODE = CLUB_CODE
                        WHERE OCBA_WEBF = 1 AND OCBA_CLUB_DEPEND = '$cid'";
       // get total number record
       $this->_totalClub = $this->getTotalClub($cid);
       
       // order by
       $qr .= " ORDER BY OCBA_CODE ASC"; 
       
       // pagination
       $qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');
       
       
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
             
       return $result;
    }
    
    /**
     * Get total record in table club
    **/
    function getTotalClub($cid){
        // Load the content if it doesn't already exist
        if ($this->_totalCoch === null) {
            
            $query = "SELECT count(DISTINCT OCBA_CODE) 
                        FROM ocba LEFT JOIN club ON OCBA_CLUB_CODE = CLUB_CODE
                        WHERE OCBA_FLAG = 1 AND OCBA_CLUB_DEPEND = '$cid'";
            
            $this->_db->setQuery($query);
            $this->_totalCoch = $this->_db->loadResult();
        }
        
        return $this->_totalCoch;
    } 
    /**
     * Get pagination for club
    **/
    function getPaginationClub($cid){
        // Load the content if it doesn't already exist
        if (empty($this->_paginationClub)) {
            jimport('joomla.html.pagination');
            $this->_paginationClub = new JPagination($this->getTotalClub($cid), $this->getState('limitstart'), $this->getState('limit') );
        }
        return $this->_paginationClub;
    }
    
    /**
     * get number of guest of year from ocba_code
    **/
    function loadCountData($cid){
       $qr = "SELECT DISTINCT USFM_CODE,USFM_NAME FROM usfm";
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       
       $qr2 = "SELECT DISTINCT USFM_CODE, USFM_NAME, USCM_NAME, USCE_DATE, USCE_VAL
                        FROM usce
                        LEFT JOIN uscm ON USCE_USCM_CODE = USCM_CODE
                        LEFT JOIN ocba ON USCE_OCBA_CODE = OCBA_CODE
                        LEFT JOIN usfm ON USCE_USFM_CODE = USFM_CODE
                        WHERE OCBA_CODE = '$cid'
                        ORDER BY USFM_CODE ASC";
       
       // load data
       $this->_db->setQuery($qr2);
       $result_val = $this->_db->loadObjectList();
       
       foreach($result as $value){
            $arr_temp = array();
            for($i = 0; $i < count($result_val); $i++){
                
                if($value->USFM_CODE == $result_val[$i]->USFM_CODE){
                    $item->USCE_VAL;
                    $item->USCE_DATE;
                    $arr_temp[] = $item;
                }
            }
            $value->VAL_COUNT = $arr_temp;
       }
       
       //echo "<pre>";
       //print_r($result);
       //echo "</pre>";
       return $result;
    }
     
    /**
     * get info gymt from ocba_code
    **/
    function loadGymt($cid){
        $qr = "SELECT GYMT_CODE,GYMT_TBGY_CODE_LIST,GYMT_MATF,GYMT_MACF,GYMT_ADMN,GYMT_METK,GYMT_SDATE,GYMT_TDATE,GYMT_CIAD,GYMT_CICI,GYMT_COAD,GYMT_COCI,GYMT_CNOT,GYMT_NOTE,GYMT_DATE
                        FROM gymt
                        WHERE GYMT_FLAG = 1 AND GYMT_WEBF = 1 AND GYMT_OCBA_CODE = '$cid'
                        ORDER BY GYMT_CODE ASC";
       
       // load data
      
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       //get data from table DISP
       $qr2 = "SELECT TBGY_CODE,TBGY_NAME FROM tbgy";
       $this->_db->setQuery($qr2);
       $disp = $this->_db->loadObjectList();
       // get gymt meeting name for gymt 1
       $master = new bgData();
       $gymt_meeting = $master->gymtMetk();
       $result[0]->GYMT_METK = $gymt_meeting[$result[0]->GYMT_METK];
       
       // get list Equipment
       $equip = $master->getListEquipment();
              
       //group data: gymt and tbgy
       foreach($result as $value){
       
           $gymt_code_list = $value->GYMT_TBGY_CODE_LIST;
           $gymt_code_list = explode(",",$gymt_code_list);
           $disabled = array();
           $equipment = array();
           for($i = 0; $i < count($gymt_code_list); $i++){
                for($j = 0; $j < count($disp); $j++){
                    if($gymt_code_list[$i] == $disp[$j]->TBGY_CODE){
                        $disabled[] = $disp[$j]->TBGY_CODE;
                        
                    }
                }
           }
           if($value->GYMT_MATF == 1){
                $equipment[] = $equip[1];
           }
           if($value->GYMT_MACF == 1){
                $equipment[] = $equip[2];
           }
           
           $value->disabled = $disabled;
           $value->equipment = $equipment;
       }
       return $result;
    }
    
    /**
     * get info pool from ocba_code
    **/
    function loadPool($cid){
        $qr = "SELECT POOL_CODE,POOL_TBPO_CODE_LIST,POOL_DISP_CODE_LIST,POOL_ADMN,POOL_METK,POOL_SDATE,POOL_TDATE,POOL_TERM,POOL_POLK,POOL_POHK,POOL_CIAD,POOL_CICI,POOL_COAD,POOL_COCI,POOL_CNOT,POOL_NOTE,POOL_DATE
                        FROM pool
                        WHERE POOL_FLAG = 1 AND POOL_WEBF = 1 AND POOL_OCBA_CODE = '$cid'";
       
       // load data
        //echo $qr;
        
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
       // get pool meeting name
       $master = new bgData();
       $pool_meeting = $master->getPoolMetk();
       $result->POOL_METK = $pool_meeting[$result->POOL_METK];
       // get water type
       $pool_pohk = $master->getPoolPohk();
       $result->POOL_POHK = $pool_pohk[$result->POOL_POHK];
       // get pool type
       $pool_polk = $master->getPoolType();
       $result->POOL_POLK = $pool_polk[$result->POOL_POLK];
       
       //get data from table tbgy
       $qr2 = "SELECT TBPO_CODE,TBPO_NAME FROM tbpo";
       $this->_db->setQuery($qr2);
       $tbpo_list = $this->_db->loadObjectList();
       
       //get data from table tbgy
       $qr3 = "SELECT DISP_CODE,DISP_NAME FROM disp";
       $this->_db->setQuery($qr3);
       $disp_list = $this->_db->loadObjectList();
                    
       //group data: POOL and TBPO
       $tbpo_code_list = $result->POOL_TBPO_CODE_LIST;
       $tbpo_code_list = explode(",",$tbpo_code_list);
       //group data: POOL and DISP
       $disp_code_list = $result->POOL_DISP_CODE_LIST;
       $disp_code_list = explode(",",$disp_code_list);
       
       $extend = array();
       $disabled = array();
       
       for($i = 0; $i < count($tbpo_code_list); $i++){
            for($j = 0; $j < count($tbpo_list); $j++){
                if($tbpo_code_list[$i] == $tbpo_list[$j]->TBPO_CODE){
                    $extend[] = $tbpo_list[$j]->TBPO_CODE;
                }
            }
       }
       for($i = 0; $i < count($disp_code_list); $i++){
            for($j = 0; $j < count($disp_list); $j++){
                if($disp_code_list[$i] == $disp_list[$j]->DISP_CODE){
                    $disabled[] = $disp_list[$j]->DISP_CODE;
                }
            }
       }
       
       $result->extend = $extend;
       $result->disabled = $disabled;
       
       return $result;
    }
    
    /**
     * get info dock from ocba_code
    **/
    function loadDock($cid){
        $qr = "SELECT DOWM_NAME,DOCK_TOYK,DOCK_SHWK,DOCK_SHHK,DOCK_CHAK,DOCK_PAKK,DOCK_TBDO_CODE_LIST,DOCK_CODE,DOCK_POST,DOCK_ADD1,DOCK_TEL,DOCK_FAX,DOCK_SDATE,DOCK_TDATE,DOCK_ADMN,DOCK_TERM,DOCM_NAME,DOCK_FTMF,DOCK_WNAME,DOCK_WNOTE,WATM_NAME,DOCK_IMGD,DOCK_IMGW,DOCK_IMGS,DOCK_CIAD,DOCK_CICI,DOCK_COAD,DOCK_COCI,DOCK_CNOT,DOCK_NOTE,DOCK_DATE,DOCK_WNAMEW
                        FROM dock
                        LEFT JOIN watm ON WATM_CODE = DOCK_WATM_CODE
                        LEFT JOIN docm ON DOCM_CODE = DOCK_DOCM_CODE
                        LEFT JOIN dowm ON DOWM_CODE=DOCK_DOWM_CODE
                        WHERE DOCK_FLAG = 1 AND DOCK_WEBF = 1 AND DOCK_OCBA_CODE = '$cid'";
       // load data
       //echo $qr;
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
       //get data from table tbgy
       $qr2 = "SELECT TBDO_CODE,TBDO_NAME FROM tbdo";
       $this->_db->setQuery($qr2);
       $disp_list = $this->_db->loadObjectList();
                                  
       //group data: DOCK and DISP
       $disp_code_list = $result->DOCK_TBDO_CODE_LIST;
       $disp_code_list = explode(",",$disp_code_list);
       $disabled = array();
       for($i = 0; $i < count($disp_code_list); $i++){
            for($j = 0; $j < count($disp_list); $j++){
                if($disp_code_list[$i] == $disp_list[$j]->TBDO_CODE){
                    $disabled[] = $disp_list[$j]->TBDO_NAME;
                }
            }
       }
       $result->disabled = $disabled;
        
       return $result;
    }
    
    /**
     * get detail coch from ocba_code
    **/
    function loadCoachDetail($cid){
        $qr = "SELECT COCH_CODE,COCH_NAME,COCH_KANA,OCBA_NAME,COCH_SEX,JOBM_NAME,COCH_IMG,COCH_B4F,COCH_LIFF,COCH_OQF,COCH_RECF,COCH_TESF,COCH_ASIF,COCH_FLOF,COCH_AQAF,COCH_LAF,COCH_LFF,COCH_WALF,COCH_OTHF,COCH_RDATE,COCH_COMM,COCH_DATE
                        FROM coch
                        LEFT JOIN ocba ON COCH_OCBA_CODE = OCBA_CODE
                        LEFT JOIN jobm ON COCH_JOBM_CODE = JOBM_CODE
                        WHERE COCH_CODE = '$cid'";
       
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
                      
       return $result;
    }
    
    /**
     * get info coch from ocba_code
    **/
    function loadCoach($cid){
                $qr = "SELECT DISTINCT COCH_CD,COCH_NAME,COCH_WOKF,COCH_CLST,COCH_OCBA_CODE,OCBA_NAME,OCBA_CODE,COCH_CODE,COCH_LDATE
                               FROM coch LEFT JOIN ocba ON coch.COCH_OCBA_CODE=ocba.OCBA_CODE                                         
                                         LEFT JOIN coli ON coch.COCH_CODE = coli.COLI_COCH_CODE
                                         
                                ";
                /*$qr="SELECT DISTINCT COCH_CD,COCH_NAME,COCH_WOKF,COCH_CLST,COCH_OCBA_CODE,OCBA_NAME,OCBA_CODE,COCH_CODE  
                            FROM coch LEFT JOIN (SELECT OCBA_CODE, OCBA_NAME FROM ocba                     
                            LEFT JOIN coli ON coch.COCH_CODE = coli.COLI_COCH_CODE)
                            AS ocba ON COCH_OCBA_CODE=OCBA_CODE";*/
                $where = array();
                $where[] = "COCH_FLAG=1";
                $where[]=" OCBA_CODE=($cid) ";
                $where[]=" (coli.COLI_COCM_CODE = 1 OR coli.COLI_COCM_CODE = 2) ";
                $where[]=" COCH_LDATE > NOW() ";
                
               if($where){
                    $where = ' WHERE '.implode(' AND ', $where);
                }else{
                    $where = null;
                }
               // get total number record
               $this->_totalCoch = $this->getTotalCoch($where);
            
                 
               $qr .= $where;
               
               // order by
               $qr .= " ORDER BY COCH_DATE DESC, COCH_CODE DESC "; 
               // pagination
               $qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');
               // load data
               //echo $qr;
        //        return;
               $this->_db->setQuery($qr);
               $result = $this->_db->loadObjectList();
               
               return $result;

    }
    
    /**
     * Get total record in table coch
    **/
    function getTotalCoch($where = null){
        // Load the content if it doesn't already exist
        if ($this->_totalCoch === null) {
            
            $query = "SELECT COUNT(DISTINCT COCH_CODE)
                        FROM coch LEFT JOIN ocba ON coch.COCH_OCBA_CODE=ocba.OCBA_CODE                                         
                        LEFT JOIN coli ON coch.COCH_CODE = coli.COLI_COCH_CODE
                        $where";
            
            $this->_db->setQuery($query);
            $this->_totalCoch = $this->_db->loadResult();
        }
        
        return $this->_totalCoch;
    } 
    /**
     * Get pagination for coch
    **/
    function getPaginationCoch($cid){
        // Load the content if it doesn't already exist
        if (empty($this->_paginationCoch)) {
            jimport('joomla.html.pagination');
            $this->_paginationCoch = new JPagination($this->getTotalCoch($cid), $this->getState('limitstart'), $this->getState('limit') );
        }
        return $this->_paginationCoch;
    }
    
    /**
     * get other info ocba from ocba_code
    **/
    function loadOther_Info($cid){
        $qr = "SELECT DISTINCT *
                        FROM ocba
                        LEFT JOIN coma ON OCBA_COMA_CODE = COMA_CODE
                        LEFT JOIN gymt ON OCBA_CODE = GYMT_OCBA_CODE
                        LEFT JOIN pool ON OCBA_CODE = POOL_OCBA_CODE
                        LEFT JOIN dock ON OCBA_CODE = DOCK_OCBA_CODE
                        LEFT JOIN (SELECT PETT_NOTE,PETT_BOAT,PETT_MINI,PETT_POOL,PETT_HOT,PETT_PHYS,PETT_CODE,PEDE_CODE,PEDE_NAME FROM pett LEFT JOIN pede ON PETT_PEDE_CODE = PEDE_CODE) AS TB ON TB.PETT_CODE = OCBA_PETT_CODE
                        WHERE OCBA_CODE = '$cid'";
       
       // load data
       //echo $qr;
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
                      
       return $result;
    }
       
        /**
     * Get all list TBDO
    **/
    function loadTbdo(){
        $qr = "SELECT * FROM tbdo";
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
    }
    
    
        /**
     * Get all list TBGY
    **/
    function loadTbgy(){
        $qr = "SELECT * FROM tbgy ";
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
    }
    
       /**
     * Get all list TBPO
    **/
    function loadTbpo(){
        $qr = "SELECT * FROM tbpo";
       // load data

       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
    }
    /**
     * 
     *         /**
     * Get all list DISP
    **/
    function loadDisp(){
        $qr = "SELECT * FROM disp";
       
       // load data
    
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
    }
    /**
     * Get all list OCBA
    **/
	function getAllCenter($list = null){

       $qr = "SELECT DISTINCT OCBA_CODE,OCBA_LNAME,OCBA_REST
                           , OCBA_NAME,OCBA_KANA, OCBA_POST, OCBA_ADD1
                           ,OCBA_ADD2,OCBA_LADD2,OCBA_TEL, OCBA_FAX
                           , OCBA_CONT,OCBA_LPOST,OCBA_LADD1,OCBA_LTEL
                           ,OCBA_LFAX,POOL_OCBA_CODE,POOL_POHK,GYMT_OCBA_CODE
                           ,DOCK_OCBA_CODE,DOCK_DOCM_CODE,OCBA_PUTF,OCBA_LARF,OCBA_CANF
                           ,OCBA_YACF,OCBA_SCHF,OCBA_OUTF
                    FROM ocba
                    LEFT JOIN gymt ON OCBA_CODE = GYMT_OCBA_CODE
                    LEFT JOIN pool ON OCBA_CODE = POOL_OCBA_CODE
                    LEFT JOIN dock ON OCBA_CODE = DOCK_OCBA_CODE
                    LEFT JOIN (
                    SELECT PREF_CODE, TOWN_CODE
                    FROM town
                    LEFT JOIN pref ON TOWN_PREF_CODE = PREF_CODE
                    ) AS town_pref ON OCBA_TOWN_CODE = town_pref.TOWN_CODE
                    LEFT JOIN (
                    SELECT PEDE_CODE, PETT_CODE
                    FROM pett
                    LEFT JOIN pede ON PETT_PEDE_CODE = PEDE_CODE
                    ) AS pett_pede ON OCBA_PETT_CODE = pett_pede.PETT_CODE";
       
       //search
       $where = array();
       $where[] = "OCBA_CODE LIKE '0%' AND OCBA_WEBF = 1";
       
       //join
       if($list['search_PREF_CODE'] != 0){
            /*$qr .= " LEFT JOIN (
                    SELECT PREF_CODE, TOWN_CODE
                    FROM town
                    LEFT JOIN pref ON TOWN_PREF_CODE = PREF_CODE
                    ) AS town_pref ON OCBA_TOWN_CODE = town_pref.TOWN_CODE";*/
            $where[] = "PREF_CODE = $list[search_PREF_CODE]";
       }
       
       if(!empty($list['search_OCBA_NAME'])){
            $where[] = " (OCBA_NAME LIKE '%$list[search_OCBA_NAME]%' OR OCBA_CODE LIKE '%$list[search_OCBA_NAME]%') ";
       }
       
       if($list['search_PEDE_CODE'] != 0){
            /*$qr .= " LEFT JOIN (
                    SELECT PEDE_CODE, PETT_CODE
                    FROM pett
                    LEFT JOIN pede ON PETT_PEDE_CODE = PEDE_CODE
                    ) AS pett_pede ON OCBA_PETT_CODE = pett_pede.PETT_CODE";*/
            $where[] = "PEDE_CODE = $list[search_PEDE_CODE]";
       }
       //search checkbox
       if(isset($list['cbbFACI1'])){
            $where[] = "GYMT_OCBA_CODE IS NOT NULL";
       }
       if(isset($list['cbbFACI2'])){
            $where[] = "DOCK_OCBA_CODE IS NOT NULL";
       }
       if(isset($list['cbbFACI3'])){
            $where[] = "POOL_OCBA_CODE IS NOT NULL";
       }
       if(isset($list['cbbFACI4'])){
            $where[] = "POOL_POHK = 1";
       }
       if(isset($list['cbbFACI5'])){
            $where[] = "POOL_POHK = 2";
       }
       if(isset($list['cbbFACI6'])){
            $where[] = "POOL_POHK = 3";
       }
       
       $sr=0;
       if($where){
            $where = ' WHERE '.implode(' AND ', $where);
            $sr=1;
       }else{
            $where = null;
       }
       // get total number record
       $this->_total = $this->getTotal($where);
         
       $qr .= $where;
       
       // order by
       $qr .= " ORDER BY OCBA_CODE ASC"; 
       
       // pagination
       $qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');
       
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
	}
    
    function getTotal($where = null){
        // Load the content if it doesn't already exist
        if ($this->_total === null) {
            
            $query = "SELECT count(DISTINCT OCBA_CODE)
                        FROM ocba
                        LEFT JOIN gymt ON OCBA_CODE = GYMT_OCBA_CODE
                        LEFT JOIN pool ON OCBA_CODE = POOL_OCBA_CODE
                        LEFT JOIN dock ON OCBA_CODE = DOCK_OCBA_CODE
                        LEFT JOIN (
                        SELECT PREF_CODE, TOWN_CODE
                        FROM town
                        LEFT JOIN pref ON TOWN_PREF_CODE = PREF_CODE
                        ) AS town_pref ON OCBA_TOWN_CODE = town_pref.TOWN_CODE
                        LEFT JOIN (
                        SELECT PEDE_CODE, PETT_CODE
                        FROM pett
                        LEFT JOIN pede ON PETT_PEDE_CODE = PEDE_CODE
                        ) AS pett_pede ON OCBA_PETT_CODE = pett_pede.PETT_CODE $where";
            
            $this->_db->setQuery($query);
            $this->_total = $this->_db->loadResult();
        }
        
        return $this->_total;
    } 
    
    function getPagination(){
        // Load the content if it doesn't already exist
        if (empty($this->_pagination)) {
            jimport('joomla.html.pagination');
            $this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit') );
        }
        return $this->_pagination;
    }
    
    /**
     * Get list pref
    **/
    function getListPref(){

	   $qr = "SELECT PREF_CODE,PREF_NAME FROM pref";
       
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
	}
    
    /**
     * Get list pede
    **/
    function getListPede(){

	   $qr = "SELECT PEDE_CODE,PEDE_NAME FROM pede";
       // order by
       $qr .= " ORDER BY PEDE_CODE ASC"; 
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       
       return $result;
	}
    
    /**
     * Get all USCM (Center)
     */
    public function getAllUscm(){
        $this->_db->setQuery("SELECT * FROM uscm WHERE USCM_FLAG = 1");
        return $this->_db->loadObjectList();
    }
    
    /**
     * Get all USFM (Center)
     */
    public function getAllUsfm(){
        $this->_db->setQuery("SELECT * FROM usfm ORDER BY USFM_CODE");
        $result = $this->_db->loadObjectList();
        return $result;
    }
    
     /**
     * Get all USFM belong to OCBA
     */
    public function getUsfmByOcba($ocba_code){

        
        $this->_db->setQuery("SELECT * FROM usfm ORDER BY USFM_CODE");
        $result = $this->_db->loadObjectList();
        // foreach($result as $key => $row){
        //     if($row->USFM_NAME == '第一体育館' && !$this->have_gym1($ocba_code)){
        //         unset($result[$key]);
        //         continue;
        //     }                
        //     if($row->USFM_NAME == '第二体育館' && !$this->have_gym2($ocba_code)){
        //         unset($result[$key]);
        //         continue;
        //     }
        //     if($row->USFM_NAME == 'プール' && !$this->have_pool($ocba_code)){
        //         unset($result[$key]);
        //         continue;
        //     }
        //     if($row->USFM_NAME == '艇庫' && !$this->have_dock($ocba_code)){
        //         unset($result[$key]);
        //         continue;
        //     }
        // }
        return $result;
    }
    
    /**
     * Check if OCBA have Gym1
     */
    public function have_gym1($ocba_code){
        $this->_db->setQuery("SELECT GYMT_CODE FROM gymt WHERE GYMT_OCBA_CODE = '$ocba_code' AND GYMT_2NDK = 1");
        $result = $this->_db->loadObjectList();
        return !empty($result);
    }
    
    /**
     * Check if OCBA have Gym2
     */
    public function have_gym2($ocba_code){
        $this->_db->setQuery("SELECT GYMT_CODE FROM gymt WHERE GYMT_OCBA_CODE = '$ocba_code' AND GYMT_2NDK = 2");
        $result = $this->_db->loadObjectList();
        return !empty($result);
    }
    
    /**
     * Check if OCBA have Pool
     */
    public function have_pool($ocba_code){
        $this->_db->setQuery("SELECT POOL_CODE FROM pool WHERE POOL_OCBA_CODE = '$ocba_code'");
        $result = $this->_db->loadObjectList();
        return !empty($result);
    }
    
    /**
     * Check if OCBA have Dock
     */
    public function have_dock($ocba_code){
        $this->_db->setQuery("SELECT DOCK_CODE FROM dock WHERE DOCK_OCBA_CODE = '$ocba_code'");
        $result = $this->_db->loadObjectList();
        return !empty($result);
    }
    
    /**
     * load USCE
    **/
    public function loadUsce($filter){
        $filter['period'] = 12;
        $filter['period_f'] = 1; 

        //get the DB object
        $db = $this->_db;
        
        //get facility
        $facility_list = $this->getUsfmByOcba($filter['id']);

        //get category
        $category = "(SELECT * FROM uscm WHERE USCM_FLAG = 1)";
        $db->setQuery($category);
        $category_list = $db->loadObjectList();
        
        //$data 
        $data = array();
        
        foreach($facility_list as $facility_key => $facility){
            $start_month = (int)$filter['month'];
            $start_year = $filter['year'];
            $end_month = "";
            $end_year = "";
            $end = "";
            
            //foreach period, calculate it's value
            for ($period = 1; $period <= $filter['period']; $period++){   
                $end_year = $start_year;
                $end_month = $start_month + $filter['period_f'] - 1;
                if($end_month > 12){
                    $end_month -= 12;
                    $end_year++;
                }
                            
                $start = $start_year."/".str_pad($start_month,2,'0',STR_PAD_LEFT);
                $end = $end_year."/".str_pad($end_month,2,'0',STR_PAD_LEFT);
                
                //push header
                $data[$facility->USFM_NAME]['head'][$period] = $start_month."月"; 
                
                //set time (for query)
                $time = $start == $end ? " AND USCE_DATE = '$start' " : " AND USCE_DATE >= '$start' AND USCE_DATE <= '$end' ";
                
                //foreach uscm avaiable
                foreach($category_list as $category_key => $category_row){   
                    $sql = 
                        "SELECT SUM(USCE_VAL) as val, MAX(USCE_UPDATE)as last_update FROM usce 
                            JOIN usfm ON USCE_USFM_CODE = USFM_CODE
                            JOIN $category as category ON USCE_USCM_CODE = USCM_CODE
                            WHERE 
                                USCE_OCBA_CODE = '$filter[id]'
                                $time
                                AND USCM_CODE = $category_row->USCM_CODE
                                AND USFM_CODE = $facility->USFM_CODE
                            ";
                    //echo $sql."<br>";
                    $db->setQuery($sql);
                    $result = $db->loadObject();
                    $val = empty($result->val) ? '-' : $result->val;
                    
                    
                    $data[$facility->USFM_NAME]['usce_data'][$category_row->USCM_CODE][$period] = $val;
                    
                    //if numf == 1 then add the value to sum_col then push the value to tmp_chart_data
                    if($category_row->USCM_NUMF == 1){
                        $data[$facility->USFM_NAME]['sum_col'][$period] += $val;
                    }
                    //add the value to sum_row (without check numf)
                    $data[$facility->USFM_NAME]['sum_row'][$category_row->USCM_CODE] += $val; 
                }
                
                //start date = end date + 1
                $start_year = $end_year;
                $start_month = $end_month + 1;
                    if($start_month > 12){
                        $start_month -= 12;
                        $start_year++;
                    }
               
            //end for period 
            }   
            
            //replace '0' -> '-' for sum (row and column)
            foreach($data[$facility->USFM_NAME]['sum_row'] as $k => $row){
                if($row == 0){
                    $data[$facility->USFM_NAME]['sum_row'][$k] = '-';
                }
            }        
            foreach($data[$facility->USFM_NAME]['sum_col'] as $k => $row){
                if($row == 0){
                    $data[$facility->USFM_NAME]['sum_col'][$k] = '-';
                }
            }
        //end foreach facility
        }
        
        $chart = array();        
        $sum = array();
        foreach($data as $facility){
            $chart_head = array();
            
            array_push($chart_head, 'ジャンル');
            foreach ($facility['head'] as $head ){
                array_push($chart_head, $head);
            }
            $count = 0;
            foreach($category_list as $category_key => $category_row){
                if($category_row->USCM_NUMF == 1){
                    $count++;
                    $sum[$category_row->USCM_CODE][0] = $category_row->USCM_NAME;
                    for ($period = 1; $period <= $filter['period']; $period++){                    
                        $sum[$category_row->USCM_CODE][$period] += $facility['usce_data'][$category_row->USCM_CODE][$period];
                    }
                }
            }
            
            $sum['sum_col'][0] = '自センター累計';
            for ($period = 1; $period <= $filter['period']; $period++){                    
                $sum['sum_col'][$period] += $facility['sum_col'][$period];
            }
            
        }
        
        array_push($chart, $chart_head);
        foreach($sum as $s){
            $tmp = array();
            foreach ($s as $val){
                array_push($tmp, $val);
            }
            array_push($chart, $tmp);
        }
        

        return array(
            'statistic_data' => $data,
            'chart_data' => array(
                'chart_data' => $chart,
                'count' => $count,
            ),
        );        
    }
    
    //load data qualification
    function loadcoli($cid)
    {
        $query="SELECT COCM_NAME,COLI_UNIT,COLI_COCH_CODE,OCBA_NAME,COLI_TERM
                        FROM cocm INNER JOIN coli ON cocm.COCM_CODE=coli.COLI_COCM_CODE 
                     			INNER JOIN ocba ON ocba.OCBA_CODE=coli.COLI_OCBA_CODE
                               WHERE coli.COLI_COCH_CODE=($cid)";
        $this->_db->setQuery($query);
        $result = $this->_db->loadObjectList();
        
        return $result;
        
    }
    
    /**
     * Get all list center
    **/
    /**
     * Get all list center
    **/
    function getAllCenterPrint($list = null){
                $qr = "SELECT DISTINCT OCBA_CODE,OCBA_LNAME, OCBA_NAME,OCBA_KANA, OCBA_POST,
                 OCBA_ADD1,OCBA_ADD2,OCBA_LADD2,OCBA_TEL, OCBA_FAX, OCBA_CONT,
                 OCBA_LPOST,OCBA_LADD1,OCBA_LTEL,OCBA_LFAX,POOL_OCBA_CODE,POOL_POHK,GYMT_OCBA_CODE
                 ,DOCK_OCBA_CODE,DOCK_DOCM_CODE,OCBA_PUTF,OCBA_LARF,OCBA_CANF,OCBA_YACF,OCBA_SCHF,OCBA_OUTF
                    FROM ocba
                    LEFT JOIN gymt ON OCBA_CODE = GYMT_OCBA_CODE
                    LEFT JOIN pool ON OCBA_CODE = POOL_OCBA_CODE
                    LEFT JOIN dock ON OCBA_CODE = DOCK_OCBA_CODE
                    INNER JOIN (
                    SELECT PREF_CODE, TOWN_CODE
                    FROM town
                    INNER JOIN pref ON TOWN_PREF_CODE = PREF_CODE
                    ) AS town_pref ON OCBA_TOWN_CODE = town_pref.TOWN_CODE
                    LEFT JOIN (
                    SELECT PEDE_CODE, PETT_CODE
                    FROM pett
                    LEFT JOIN pede ON PETT_PEDE_CODE = PEDE_CODE
                    ) AS pett_pede ON OCBA_PETT_CODE = pett_pede.PETT_CODE";
                   $where = array();
                   $where[] = "OCBA_CODE LIKE '0%' AND OCBA_FLAG = 1 AND OCBA_WEBF = 1";
                   if($list['search_PREF_CODE'] != 0){
                        $where[] = "PREF_CODE = $list[search_PREF_CODE]";
                   }
                   
                   if(!empty($list['search_OCBA_NAME'])){
                        $where[] = " (OCBA_NAME LIKE '%$list[search_OCBA_NAME]%' OR OCBA_CODE LIKE '%$list[search_OCBA_NAME]%') ";
                   }
                   
                   if($list['search_PEDE_CODE'] != 0){
                        $where[] = "PEDE_CODE = $list[search_PEDE_CODE]";
                   }
                   
                    //search checkbox
                   if(isset($list['cbbFACI1'])){
                        $where[] = "GYMT_OCBA_CODE IS NOT NULL";
                   }
                   if(isset($list['cbbFACI2'])){
                        $where[] = "DOCK_OCBA_CODE IS NOT NULL";
                   }
                   if(isset($list['cbbFACI3'])){
                        $where[] = "POOL_OCBA_CODE IS NOT NULL";
                   }
                   if(isset($list['cbbFACI4'])){
                        $where[] = "POOL_POHK = 1";
                   }
                   if(isset($list['cbbFACI5'])){
                        $where[] = "POOL_POHK = 2";
                   }
                   if(isset($list['cbbFACI6'])){
                        $where[] = "POOL_POHK = 3";
                   }

                   if($where){
                        $where = ' WHERE '.implode(' AND ', $where);
                    }else{
                        $where = null;
                    }
                     
                   $qr .= $where;
                   
                   // order by
                   $qr .= " ORDER BY OCBA_CODE ASC"; 
                   /*echo "<pre>";
                   var_dump($qr);
                   echo "</pre>";
                   die;*/
                   
                   $this->_db->setQuery($qr);
                   $result = $this->_db->loadObjectList();
        
       return $result;
  }

    /*function getAllCenterPrint($list = null){
        
       $qr = "SELECT DISTINCT PREF_CODE,PREF_NAME
                    FROM pref INNER JOIN town ON TOWN_PREF_CODE=PREF_CODE
                              INNER JOIN ocba ON OCBA_TOWN_CODE=TOWN_CODE
              ";  
        $where = array();
        if($list['search_PREF_CODE'] != 0){
            $where[] = "PREF_CODE = '$list[search_PREF_CODE]'";
        }
        if($where){
            $where = ' WHERE '.implode(' AND ', $where);
        }else{
            $where = null;
        }
        $qr .= $where;
       // order by
       $qr .= " ORDER BY PREF_CODE DESC"; 
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       //load data info
        foreach($result as $k => $row)
            {
                $a= $row->PREF_CODE;
                
                $qr = "SELECT DISTINCT OCBA_CODE,OCBA_LNAME, OCBA_NAME,OCBA_KANA, OCBA_POST, OCBA_ADD1,OCBA_ADD2,OCBA_LADD2,OCBA_TEL, OCBA_FAX, OCBA_CONT,OCBA_LPOST,OCBA_LADD1,OCBA_LTEL,OCBA_LFAX,POOL_OCBA_CODE,GYMT_OCBA_CODE,DOCK_OCBA_CODE,OCBA_PUTF,OCBA_LARF,OCBA_CANF,OCBA_YACF,OCBA_SCHF,OCBA_OUTF
                    FROM ocba
                    LEFT JOIN gymt ON OCBA_CODE = GYMT_OCBA_CODE
                    LEFT JOIN pool ON OCBA_CODE = POOL_OCBA_CODE
                    LEFT JOIN dock ON OCBA_CODE = DOCK_OCBA_CODE
                    INNER JOIN (
                    SELECT PREF_CODE, TOWN_CODE
                    FROM town
                    INNER JOIN pref ON TOWN_PREF_CODE = PREF_CODE
                    ) AS town_pref ON OCBA_TOWN_CODE = town_pref.TOWN_CODE
                    LEFT JOIN (
                    SELECT PEDE_CODE, PETT_CODE
                    FROM pett
                    LEFT JOIN pede ON PETT_PEDE_CODE = PEDE_CODE
                    ) AS pett_pede ON OCBA_PETT_CODE = pett_pede.PETT_CODE";
                   $where = array();
                    $where[] = "PREF_CODE=$a";
                   $where[] = "OCBA_CODE LIKE '0%' AND OCBA_FLAG = 1 AND OCBA_WEBF = 1";
                   if($list['search_PREF_CODE'] != 0){
                        $where[] = "PREF_CODE = $list[search_PREF_CODE]";
                   }
                   
                   if(!empty($list['search_OCBA_NAME'])){
                        $where[] = "OCBA_NAME LIKE '%$list[search_OCBA_NAME]%' OR OCBA_CODE LIKE '%$list[search_OCBA_NAME]%'";
                   }
                   
                   if($list['search_PEDE_CODE'] != 0){
                        $where[] = "PEDE_CODE = $list[search_PEDE_CODE]";
                   }
                   //search checkbox
                   if(isset($list['cbbFACI1'])){
                        $where[] = "GYMT_OCBA_CODE IS NOT NULL";
                   }
                   if(isset($list['cbbFACI2'])){
                        $where[] = "POOL_POLK = 3";
                   }
                   if(isset($list['cbbFACI3'])){
                        $where[] = "DOCK_OCBA_CODE IS NOT NULL";
                   }
                   if(isset($list['cbbFACI4'])){
                        $where[] = "POOL_POHK = 2";
                   }
                   if(isset($list['cbbFACI5'])){
                        $where[] = "POOL_POHK = 3";
                   }
                   if($where){
                        $where = ' WHERE '.implode(' AND ', $where);
                    }else{
                        $where = null;
                    }
                     
                   $qr .= $where;
                   
                   // order by
                   $qr .= " ORDER BY OCBA_CODE ASC"; 
                   
                   $this->_db->setQuery($qr);
                   $data = $this->_db->loadObjectList();
                   if(empty($data)){
                      unset($result[$k]);
                      continue;
                   }
                   $row->data = $data;
                 
            }
        
       return $result;
	}*/

}
